using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Xml.Serialization;
using System.Xml.Schema;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

using CoreLab.Common;
using CoreLab.Oracle;
using Topobase.Wastewater.Isybau;

namespace ISYBAUSchemaExporter
{
    public partial class ExporterForm : Form
    {
        private readonly string EN_ISYBAU_SCHEMA_PATH = @"..\..\..\..\ISYBAU-XML-SCHEMA\EN\0610-metadaten.xsd";
        private readonly string DE_ISYBAU_SCHEMA_PATH = @"..\..\..\..\ISYBAU-XML-SCHEMA\DE\0610-metadaten.xsd";
        private readonly string SAMP_ISYBAU_FILE_PATH = @"..\..\..\..\ISYBAU-XML-SCHEMA\Sampledata\ISY-XML-Inspektion-DIN_EN_13508-2.xml";
		 private readonly string EXCEL_FILE_OUTPUT = @"E:\p4root\isd\Dev\TB\Topobase\Developers\johnwang\ISYBAU-XML-SCHEMA\isybau.xlsx";
        private readonly string SQL_FILE_OUTPUT_DE = @"..\..\..\..\..\ISYBAU-XML-SCHEMA\isybau_de.sql";
        private readonly string SQL_FILE_OUTPUT_EN = @"..\..\..\..\..\ISYBAU-XML-SCHEMA\isybau_en.sql";
        private readonly string DROP_FILE_OUTPUT_DE = @"..\..\..\..\..\ISYBAU-XML-SCHEMA\isybau_drop_de.sql";
        private readonly string DROP_FILE_OUTPUT_EN = @"..\..\..\..\..\ISYBAU-XML-SCHEMA\isybau_drop_en.sql";
        private readonly string DE_EN_TABLELIST_FILE = @"..\..\..\..\ISYBAU-XML-SCHEMA\ISYBAU_EN_DE_TableNameList.xlsx";
        private readonly string DE_EN_TABLELIST_XML_FILE = @"..\..\..\..\ISYBAU-XML-SCHEMA\ISYBAU_EN_DE_TableNameList.xml";
        
        public readonly string XMLELEMENT_TABLE = "table";
        public readonly string XMLELEMENT_TYPE = "type";
        public readonly string XMLELEMENT_TYPE_FEATURE = "feature";
        public readonly string XMLELEMENT_TYPE_DOMAIN = "domain";
        public readonly string XMLELEMENT_TYPE_FIELD_REFERENCE = "reference";
        public readonly string XMLELEMENT_TYPE_FIELD_COLLECTION = "collection";
        public readonly string XMLELEMENT_TYPE_FIELD_COMMON = "common";

        public readonly string XMLELEMENT_TITLE = "title";
        public readonly string XMLELEMENT_EN = "en";
        public readonly string XMLELEMENT_DE = "de";
        public readonly string XMLELEMENT_FIELDS = "fields";
        public readonly string XMLELEMENT_FIELD = "field";

		 private readonly string ORACLE_HOST = "pseb-gbu-tb01";
		 private readonly string ORACLE_SERVICE = "orcl_my";
        private readonly string ORACLE_USER = "ISYBAU_SCHEMA";
        private readonly string ORACLE_PASS = "isybau";

        private bool mBackWorkerResult = false;
        private bool mImportWorkResult = false;

        public ExporterForm()
        {
            InitializeComponent();
        }

        private void button_browse_Click(object sender, EventArgs e)
        {
            
        }

        private void btn_export_Click(object sender, EventArgs e)
        {
            ChangeControls(false);
            this.timer_progress.Tick += new System.EventHandler(this.timer_progress_Tick);
            backgroundWorker_excel.DoWork += new DoWorkEventHandler(backgroundWorker_excel_DoWork);
            backgroundWorker_excel.RunWorkerCompleted += new RunWorkerCompletedEventHandler(backgroundWorker_excel_RunWorkerCompleted);
            backgroundWorker_excel.RunWorkerAsync();
            toolStripStatusLabel_main.Text = "Start exporting to excel format ...";
        }

        private void backgroundWorker_excel_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            this.timer_progress.Tick -= new System.EventHandler(this.timer_progress_Tick);
            this.toolStripProgressBar_main.Value = 0;
            if (mBackWorkerResult)
            {
                this.toolStripStatusLabel_main.Text = "Export to excel completed successful.";
            }
            else
            {
                this.toolStripStatusLabel_main.Text = "Export to excel failed.";
            }
            ChangeControls(true);
        }
                
        private void backgroundWorker_excel_DoWork(object sender, DoWorkEventArgs e)
        {
            backgroundWorker_excel.DoWork -= new DoWorkEventHandler(backgroundWorker_excel_DoWork);
            mBackWorkerResult = export2excel(DE_ISYBAU_SCHEMA_PATH, EXCEL_FILE_OUTPUT);

        }

        private void backgroundWorker_oracle_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            backgroundWorker_excel.RunWorkerCompleted -= new RunWorkerCompletedEventHandler(backgroundWorker_oracle_RunWorkerCompleted);
            this.timer_progress.Tick -= new System.EventHandler(this.timer_progress_Tick);
            this.toolStripProgressBar_main.Value = 0;
            if (mBackWorkerResult)
            {
                this.toolStripStatusLabel_main.Text = "Export to oracle completed successful.";
            }
            else
            {
                this.toolStripStatusLabel_main.Text = "Export to oracle failed.";
            }
            ChangeControls(true);
        }
        
        private void backgroundWorker_oracle_DoWork(object sender, DoWorkEventArgs e)
        {
            backgroundWorker_excel.DoWork -= new DoWorkEventHandler(backgroundWorker_oracle_DoWork);
            string enSchemaPath = ConvertRel2AbsPath(EN_ISYBAU_SCHEMA_PATH);
            string deSchemaPath = ConvertRel2AbsPath(DE_ISYBAU_SCHEMA_PATH);
            string deSqlOutput = ConvertRel2AbsPath(SQL_FILE_OUTPUT_DE);
            string enSqlOutput = ConvertRel2AbsPath(SQL_FILE_OUTPUT_EN);
            string deDropOutput = ConvertRel2AbsPath(DROP_FILE_OUTPUT_DE);
            string enDropOutput = ConvertRel2AbsPath(DROP_FILE_OUTPUT_EN);
            string tableListFile = ConvertRel2AbsPath(DE_EN_TABLELIST_XML_FILE);

            mBackWorkerResult = export2oracle(enSchemaPath, deSchemaPath, deSqlOutput, enSqlOutput, deDropOutput, enDropOutput, tableListFile);
        }

        private void timer_progress_Tick(object sender, EventArgs e)
        {
            toolStripProgressBar_main.Value = (toolStripProgressBar_main.Value == 100 ? 0 : toolStripProgressBar_main.Value + 10);
        }

        private void ChangeControls(bool status)
        {
            textBox_schemaPath.Enabled = status;
            button_browse.Enabled = status;
            btn_export.Enabled = status;
        }

        private string ConvertRel2AbsPath(string relativePath)
        {
            return Path.GetFullPath(Directory.GetCurrentDirectory() + relativePath);
        }

        private bool export2excel(string isybauSchemaPath, string outputExcelFilePath)
        {
            bool result = false;        

            try
            {
                Excel.Application oExcelApp;
                Excel.Workbook oWorkBook;
                Excel.Worksheet oWorkSheet;

                oExcelApp = new Microsoft.Office.Interop.Excel.Application();
                oExcelApp.Visible = false;

                oWorkBook = oExcelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                oWorkSheet = oWorkBook.ActiveSheet as Excel.Worksheet;

					 OracleSchemaVisitor englishVisitor = new OracleSchemaVisitor();
					 ISYBAUSchemaParser englishParser = new ISYBAUSchemaParser(EN_ISYBAU_SCHEMA_PATH, englishVisitor);
					 result = englishParser.Parse();

					 OracleSchemaVisitor germanVisitor = new OracleSchemaVisitor();
					 ISYBAUSchemaParser germanParser = new ISYBAUSchemaParser(DE_ISYBAU_SCHEMA_PATH, germanVisitor);
					 result = germanParser.Parse();
					 exportDeAndEnList2Excel(germanVisitor.TableDictionary, englishVisitor.TableDictionary, outputExcelFilePath);
					/*
                ExcelVisitor visitor = new ExcelVisitor(oWorkSheet);

                ISYBAUSchemaParser parser = new ISYBAUSchemaParser(isybauSchemaPath, visitor);
                result = parser.Parse();
					 
 
                if (result)
                {
                    if (System.IO.File.Exists(outputExcelFilePath))
                    {
                        System.IO.File.Delete(outputExcelFilePath);
                    }
                    oWorkBook.SaveAs(outputExcelFilePath, Excel.XlFileFormat.xlWorkbookDefault, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
                }
                oWorkBook.Close(false, string.Empty, null);
                oExcelApp = null;
					 */

                result = true;
            }
            catch (Exception ex)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, ex.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, ex.Source);

                MessageBox.Show(errorMessage, "Error");
            }
            return result;
        }

        private bool exportDeAndEnList2Excel(Dictionary<string, OracleSchemaTable> deDictionary, Dictionary<string, OracleSchemaTable> enDictionary, string outputExcelPath)
        {
             bool result = false;        

            try
            {
                Excel.Application oExcelApp;
                Excel.Workbook oWorkBook;
                Excel.Worksheet oWorkSheet;

                oExcelApp = new Microsoft.Office.Interop.Excel.Application();
                oExcelApp.Visible = false;

                oWorkBook = oExcelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
                oWorkSheet = oWorkBook.ActiveSheet as Excel.Worksheet;

                Dictionary<string, OracleSchemaTable>.Enumerator englishEnumerator = enDictionary.GetEnumerator();
                Dictionary<string, OracleSchemaTable>.Enumerator germanEnumerator = deDictionary.GetEnumerator();

                int index = 1;
                oWorkSheet.Cells[index, 1] = "German Name";
                oWorkSheet.Cells[index, 4] = "English Name";
                index++;
                while (germanEnumerator.MoveNext())
                {
                    englishEnumerator.MoveNext();

                    OracleSchemaTable germanTable = germanEnumerator.Current.Value;
                    OracleSchemaTable englishTable = englishEnumerator.Current.Value;

                    if (germanTable.Initialized)
                    {
                        index = GenerateDeEnList(germanTable, englishTable, oWorkSheet, index);
                    }
                }
                if (index > 1)
                {
                    result = true;
                }
                
                if (result)
                {
                    if (System.IO.File.Exists(outputExcelPath))
                    {
                        System.IO.File.Delete(outputExcelPath);
                    }

                    oWorkBook.SaveAs(outputExcelPath, Excel.XlFileFormat.xlWorkbookDefault, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
                    oWorkBook.Close(false, string.Empty, null);
                }
                
                oExcelApp = null;
                result = true;
            }
            catch (Exception ex)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, ex.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, ex.Source);

                MessageBox.Show(errorMessage, "Error");
            }
            return result;
        }

        private int GenerateDeEnList(OracleSchemaTable deTable, OracleSchemaTable enTable, Excel.Worksheet sheet, int index)
        {          
            if (deTable is OracleSchemaDomainTable)
            {
					index++;
					sheet.Cells[index, 1] = deTable.DatabaseName;
					sheet.Cells[index, 2] = "[Domain]";
					sheet.Cells[index, 4] = enTable.DatabaseName;
					sheet.Cells[index, 5] = "[Domain]";

					string startCell = string.Format("A{0}", index);
					string endCell = string.Format("Z{0}", index);
					sheet.get_Range(startCell, endCell).Font.Bold = true;

					index++;

                OracleSchemaDomainTable deDomainTable = deTable as OracleSchemaDomainTable;
                OracleSchemaDomainTable enDomainTable = enTable as OracleSchemaDomainTable;
                List<OracleSchemaDomainValue>.Enumerator deDoEnum = deDomainTable.EnumValues.GetEnumerator();
                List<OracleSchemaDomainValue>.Enumerator enDoEnum = enDomainTable.EnumValues.GetEnumerator();
                while (deDoEnum.MoveNext())
                {
                    enDoEnum.MoveNext();
                    sheet.Cells[index, 1] = deDoEnum.Current.ShortValue;
                    sheet.Cells[index, 2] = deDoEnum.Current.Value;
                    sheet.Cells[index, 4] = enDoEnum.Current.ShortValue;
                    sheet.Cells[index, 5] = enDoEnum.Current.Value;
                    index++;
                }
            }
				/*
			else
			{
				 if (enTable.Fields.Count > 0)
				 {
					  List<OracleSchemaField>.Enumerator enEnumerator = enTable.Fields.GetEnumerator();
					  List<OracleSchemaField>.Enumerator deEnumerator = deTable.Fields.GetEnumerator();
					  while (deEnumerator.MoveNext())
					  {
							enEnumerator.MoveNext();

							sheet.Cells[index, 2] = deEnumerator.Current.DatabaseName;
							sheet.Cells[index, 5] = enEnumerator.Current.DatabaseName;
							if (deEnumerator.Current is OracleSchemaReferenceField)
							{
								 sheet.Cells[index, 3] = "FK of " + (deEnumerator.Current as OracleSchemaReferenceField).ReferencedTable.DatabaseName;
								 sheet.Cells[index, 6] = "FK of " + (enEnumerator.Current as OracleSchemaReferenceField).ReferencedTable.DatabaseName;
							}
							index++;
					  }
				 }
			}
					 
 **/
			if (deTable.Fields.Count > 0)
			{
				 List<OracleSchemaField>.Enumerator enEnumerator = enTable.Fields.GetEnumerator();
				 List<OracleSchemaField>.Enumerator deEnumerator = deTable.Fields.GetEnumerator();

				 while (deEnumerator.MoveNext())
				 {
					  enEnumerator.MoveNext();

					  if (deEnumerator.Current is OracleSchemaCollectionField)
					  {
							OracleSchemaTable deCollTable = (deEnumerator.Current as OracleSchemaCollectionField).CollectionTable;
							OracleSchemaTable enCollTable = (enEnumerator.Current as OracleSchemaCollectionField).CollectionTable;
							index = GenerateDeEnList(deCollTable, enCollTable, sheet, index);
					  }
				 }
			}
				
				return index;
        }

        private bool export2oracle(string isybauSchemaPath_EN, string isybauSchemaPath_DE, string deOutputTextFilePath, string enOutputTextFilePath, string deDropOutputFilePath, string enDropOutputFilePath, string tableNameListFilePath)
        {
            string alterTableSqlFile = @"E:\P4Root\isd\Dev\TB\Topobase\Developers\johnwang\ISYBAU-XML-SCHEMA\isybau_alter.sql";

            bool result = false;
            try {
                Dictionary<string, OracleSchemaTable> englishDictionary;
                Dictionary<string, OracleSchemaTable> germanDictionary;

                OracleSchemaVisitor englishVisitor = new OracleSchemaVisitor();
                ISYBAUSchemaParser englishParser = new ISYBAUSchemaParser(isybauSchemaPath_EN, englishVisitor);
                result = englishParser.Parse();                
                
                if (result)
                {
                    englishDictionary = englishVisitor.TableDictionary;

                    if (System.IO.File.Exists(deOutputTextFilePath))
                    {
                        System.IO.File.Delete(deOutputTextFilePath);
                    }

                    if (System.IO.File.Exists(enOutputTextFilePath))
                    {
                        System.IO.File.Delete(enOutputTextFilePath);
                    }

                    if (System.IO.File.Exists(deDropOutputFilePath))
                    {
                        System.IO.File.Delete(deDropOutputFilePath);
                    }

                    if (System.IO.File.Exists(enDropOutputFilePath))
                    {
                        System.IO.File.Delete(enDropOutputFilePath);
                    }

                    if (System.IO.File.Exists(alterTableSqlFile))
                    {
                        System.IO.File.Delete(alterTableSqlFile);
                    }

                    OracleSchemaVisitor germanVisitor = new OracleSchemaVisitor();
                    ISYBAUSchemaParser germanParser = new ISYBAUSchemaParser(isybauSchemaPath_DE, germanVisitor);
                    result = germanParser.Parse();

                    StreamWriter deSW = new StreamWriter(deOutputTextFilePath);
                    StreamWriter enSW = new StreamWriter(enOutputTextFilePath);
                    StreamWriter deDropSW = new StreamWriter(deDropOutputFilePath);
                    StreamWriter enDropSW = new StreamWriter(enDropOutputFilePath);
                    string deSqlStatement = germanVisitor.DumpResult();
                    string enSqlStatement = englishVisitor.DumpResult();
                    string deDropStatement = germanVisitor.DumpDropResult();
                    string enDropStatement = englishVisitor.DumpDropResult();

                    StreamWriter alterSW = new StreamWriter(alterTableSqlFile);

                    deSW.Write(deSqlStatement);
                    deSW.Close();

                    enSW.Write(enSqlStatement);
                    enSW.Close();

                    deDropSW.Write(deDropStatement);
                    deDropSW.Close();

                    enDropSW.Write(enDropStatement);
                    enDropSW.Close();
                    result = true;                    
                    
                    string tableList = string.Empty;
                    if (result)
                    {
                        germanDictionary = germanVisitor.TableDictionary;
                        Dictionary<string, OracleSchemaTable>.Enumerator englishEnumerator = englishDictionary.GetEnumerator();
                        Dictionary<string, OracleSchemaTable>.Enumerator germanEnumerator = germanDictionary.GetEnumerator();
                        
                        XmlDocument xmlDoc = new XmlDocument();
                        xmlDoc.AppendChild(xmlDoc.CreateXmlDeclaration("1.0", null, null));
                        xmlDoc.AppendChild(xmlDoc.CreateElement("tables"));

                        
                        while (germanEnumerator.MoveNext())
                        {
                            englishEnumerator.MoveNext();

                            if (germanEnumerator.Current.Value != null && englishEnumerator.Current.Value != null)
                            {
                                OracleSchemaTable germanTable = germanEnumerator.Current.Value;
                                OracleSchemaTable englishTable = englishEnumerator.Current.Value;

                                if (germanTable.Initialized)
                                {
                                    GenerateAlterSql(germanTable, englishTable, alterSW);
                                    /*
                                    if (germanTable is OracleSchemaDomainTable)
                                    {
                                        XmlElement domainTableElement = CreateNodeElement(englishTable, germanTable, xmlDoc, XMLELEMENT_TABLE, XMLELEMENT_TYPE_DOMAIN);
                                        xmlDoc.DocumentElement.AppendChild(domainTableElement);
                                    }
                                    else
                                    {
                                        XmlElement tableElement = CreateTableElement(englishTable, germanTable, xmlDoc);
                                        xmlDoc.DocumentElement.AppendChild(tableElement);
                                    }
                                    */
                                }
                            }
                        }

                        alterSW.Close();

                        if (System.IO.File.Exists(tableNameListFilePath))
                        {
                            System.IO.File.Delete(tableNameListFilePath);
                        }
                        xmlDoc.PreserveWhitespace = false;
                        xmlDoc.Save(tableNameListFilePath);

                        //result = exportDeAndEnList2Excel(germanDictionary, englishDictionary, DE_EN_TABLELIST_FILE);
                        result = true;
                    }                    
                }                
            }
            catch (Exception ex)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, ex.Message);
                errorMessage = String.Concat(errorMessage, " Inner: ");
                errorMessage = String.Concat(errorMessage, ex.InnerException.Message);

                MessageBox.Show(errorMessage, "Error");
            }
            return result;
        }

        private void GenerateAlterSql(OracleSchemaTable deTable, OracleSchemaTable enTable, StreamWriter sw)
        {
            string alterTableStatement = string.Empty;
            if (!deTable.DatabaseName.Equals(enTable.DatabaseName))
            {
                alterTableStatement = AlterTableName(deTable.DatabaseName, enTable.DatabaseName);
            }
            if (deTable is OracleSchemaDomainTable)
            {
                sw.WriteLine(alterTableStatement);
            }
            else
            {
                if (enTable.Fields.Count > 0)
                {
                    List<OracleSchemaField>.Enumerator enEnumerator = enTable.Fields.GetEnumerator();
                    List<OracleSchemaField>.Enumerator deEnumerator = deTable.Fields.GetEnumerator();
                    while (deEnumerator.MoveNext())
                    {
                        enEnumerator.MoveNext();
                        if (enEnumerator.Current is OracleSchemaChoiceField)
                        {
                            sw.WriteLine(AlterChoiceColumnValues(deEnumerator.Current as OracleSchemaChoiceField, enEnumerator.Current as OracleSchemaChoiceField, deTable.DatabaseName));
                        }

                        if ( enEnumerator.Current.Name == OracleSchemaVisitor.FIELD_AUTO_ID || deEnumerator.Current.DatabaseName.Equals(enEnumerator.Current.DatabaseName))
                        {
                            continue;
                        }                        
                        sw.WriteLine(AlterColumnName(deTable.DatabaseName, deEnumerator.Current.DatabaseName, enEnumerator.Current.DatabaseName));                            
                    }
                }
                sw.WriteLine(alterTableStatement);
            }
            if (enTable is OracleSchemaDomainTable)
            {
                OracleSchemaDomainTable domainTable = enTable as OracleSchemaDomainTable;
                sw.WriteLine(domainTable.UpdateValues());
            }

            if (deTable.Fields.Count > 0)
            {
                List<OracleSchemaField>.Enumerator enEnumerator = enTable.Fields.GetEnumerator();
                List<OracleSchemaField>.Enumerator deEnumerator = deTable.Fields.GetEnumerator();
            
                while (deEnumerator.MoveNext())
                {
                    enEnumerator.MoveNext();

                    if (deEnumerator.Current is OracleSchemaCollectionField)
                    {
                        OracleSchemaTable deCollTable = (deEnumerator.Current as OracleSchemaCollectionField).CollectionTable;
                        OracleSchemaTable enCollTable = (enEnumerator.Current as OracleSchemaCollectionField).CollectionTable;
                        GenerateAlterSql(deCollTable, enCollTable, sw);
                    }
                }
            }            
        }

        private string AlterTableName(string oldName, string newName)
        {
            return string.Format("ALTER TABLE {0} RENAME TO {1};", oldName, newName);
        }

        private string AlterColumnName(string tableName, string oldName, string newName)
        {
            return string.Format("ALTER TABLE {0} RENAME COLUMN {1} TO {2};", tableName, oldName, newName);
        }

        private string AlterChoiceColumnValues(OracleSchemaChoiceField deChoiceField, OracleSchemaChoiceField enChoiceField, string tableName)
        {
            Dictionary<string, string> deChoiceTypeList = deChoiceField.ChoicedTypeList;
            Dictionary<string, string> enChoiceTypeList = enChoiceField.ChoicedTypeList;
            Dictionary<string, string>.Enumerator deEnumerator = deChoiceTypeList.GetEnumerator();
            Dictionary<string, string>.Enumerator enEnumerator = enChoiceTypeList.GetEnumerator();
            string updateState = String.Empty;
            while (deEnumerator.MoveNext())
            {
                enEnumerator.MoveNext();
                if (!enEnumerator.Current.Equals(deEnumerator.Current) && !enEnumerator.Current.Equals(string.Empty))
                {
                    updateState += string.Format("UPDATE {0} SET {1}='{2}' WHERE {1}='{3}';{4}", tableName, deChoiceField.TypeFieldName, enEnumerator.Current.Value, deEnumerator.Current.Value, Environment.NewLine);
                }
            }
            return updateState;
        }

        private void WriteTableList(string filePath, Dictionary<string, OracleSchemaTable>.Enumerator enEnumerator, Dictionary<string, OracleSchemaTable>.Enumerator deEnumerator)
        {
            if (System.IO.File.Exists(filePath))
            {
                System.IO.File.Delete(filePath);
            }
            StreamWriter sw = new StreamWriter(filePath);
            int index = 0;

            while (deEnumerator.MoveNext())
            {
                enEnumerator.MoveNext();
                OracleSchemaTable deTable = deEnumerator.Current.Value;
                OracleSchemaTable enTable = enEnumerator.Current.Value;
                sw.WriteLine(string.Format("{0}:[DE]{1}", index, deTable.Name));
                sw.WriteLine(string.Format("{0}:[EN]{1}", index, enTable.Name));
                index++;
            }
            sw.Close();
        }

        private void button_oracle_Click(object sender, EventArgs e)
        {
            ChangeControls(false);
            this.timer_progress.Tick += new System.EventHandler(this.timer_progress_Tick);
            backgroundWorker_excel.DoWork += new DoWorkEventHandler(backgroundWorker_oracle_DoWork);
            backgroundWorker_excel.RunWorkerCompleted += new RunWorkerCompletedEventHandler(backgroundWorker_oracle_RunWorkerCompleted);
            backgroundWorker_excel.RunWorkerAsync();
            toolStripStatusLabel_main.Text = "Start exporting to oracle format ...";
        }
        
        private XmlElement CreateTableElement(OracleSchemaTable enTable, OracleSchemaTable deTable, XmlDocument xmlDoc)
        {
            XmlElement tableElement = CreateNodeElement(enTable, deTable, xmlDoc, XMLELEMENT_TABLE, XMLELEMENT_TYPE_FEATURE);
            XmlElement fieldsElement = xmlDoc.CreateElement(XMLELEMENT_FIELDS);
            if (enTable.Fields.Count > 0)
            {
                List<OracleSchemaField>.Enumerator enEnumerator = enTable.Fields.GetEnumerator();
                List<OracleSchemaField>.Enumerator deEnumerator = deTable.Fields.GetEnumerator();
                string itemType = XMLELEMENT_TYPE_FIELD_COMMON;

                while (deEnumerator.MoveNext())
                {
                    enEnumerator.MoveNext();
                    if (enEnumerator.Current != null && deEnumerator.Current != null)
                    {
                        if (enEnumerator.Current.Name != OracleSchemaVisitor.FIELD_AUTO_ID)
                        {
                            if (enEnumerator.Current is OracleSchemaReferenceField)
                            {
                                itemType = XMLELEMENT_TYPE_FIELD_REFERENCE;
                            }

                            XmlElement fieldElement = CreateNodeElement(enEnumerator.Current, deEnumerator.Current, xmlDoc, XMLELEMENT_FIELD, itemType);
                            fieldsElement.AppendChild(fieldElement);
                        }
                    }
                }
            }
            tableElement.AppendChild(fieldsElement);
            return tableElement;
        }

        private XmlElement CreateNodeElement(OracleSchemaNode enField, OracleSchemaNode deField, XmlDocument xmlDoc, string nodeType, string itemType)
        {
            XmlElement result = xmlDoc.CreateElement(nodeType);

            XmlElement typeElement = xmlDoc.CreateElement(XMLELEMENT_TYPE);
            typeElement.AppendChild(xmlDoc.CreateTextNode(itemType));

            XmlElement titleElement = xmlDoc.CreateElement(XMLELEMENT_TITLE);
            
            XmlElement enTitle = xmlDoc.CreateElement(XMLELEMENT_EN);
            enTitle.AppendChild(xmlDoc.CreateTextNode(enField.DatabaseName));

            XmlElement deTitle = xmlDoc.CreateElement(XMLELEMENT_DE);
            deTitle.AppendChild(xmlDoc.CreateTextNode(deField.OriginalSchemaName()));

            titleElement.AppendChild(enTitle);
            titleElement.AppendChild(deTitle);

            result.AppendChild(typeElement);
            result.AppendChild(titleElement);
            return result;
        }

        private void button_import_Click(object sender, EventArgs e)
        {
            
            ChangeControls(false);
            this.timer_progress.Tick += new EventHandler(timer_progress_Tick);
            backgroundWorker_excel.DoWork +=new DoWorkEventHandler(backgroundWorker_import_DoWork);
            backgroundWorker_excel.RunWorkerCompleted += new RunWorkerCompletedEventHandler(backgroundWorker_import_RunWorkerCompleted);
            backgroundWorker_excel.RunWorkerAsync();
            toolStripStatusLabel_main.Text = "Start importing ISYBAU Xml data ...";
        }

        private void backgroundWorker_import_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            backgroundWorker_excel.RunWorkerCompleted -= new RunWorkerCompletedEventHandler(backgroundWorker_import_RunWorkerCompleted);
            this.timer_progress.Tick -= new System.EventHandler(this.timer_progress_Tick);
            this.toolStripProgressBar_main.Value = 0;

            if (mImportWorkResult)
            {
                this.toolStripStatusLabel_main.Text = "Import to oracle completed successful.";
            }
            else
            {
                this.toolStripStatusLabel_main.Text = "Import to oracle failed.";
            }
            ChangeControls(true);
        }

        private void backgroundWorker_import_DoWork(object sender, DoWorkEventArgs e)
        {
            backgroundWorker_excel.DoWork -= new DoWorkEventHandler(backgroundWorker_import_DoWork);
           
            string enSchemaPath = ConvertRel2AbsPath(DE_ISYBAU_SCHEMA_PATH);
            mImportWorkResult = import2Oracle(enSchemaPath);
        }

        private bool import2Oracle(string isybauSchemaPath)
        {
            int result = -1;
            
            try
            {
                Dictionary<string, OracleSchemaTable> tableDictionary;

                OracleSchemaVisitor visitor = new OracleSchemaVisitor();
                ISYBAUSchemaParser parser = new ISYBAUSchemaParser(isybauSchemaPath, visitor);
                bool parseResult = parser.Parse();

                if (parseResult)
                {
                    tableDictionary = visitor.TableDictionary;
                    OracleConnection conn = InitialOracleConnection(ORACLE_HOST, ORACLE_SERVICE, ORACLE_USER, ORACLE_PASS);
                    if (conn != null)
                    {
                        OracleImporter importer = new OracleImporter(tableDictionary, conn);
                        string sampleFilePath = ConvertRel2AbsPath(SAMP_ISYBAU_FILE_PATH);
                        Identifikation isybauObject = GetIsybauObject(sampleFilePath);
                        result = importer.Import(isybauObject, null);
                        conn.Close();
                    }   
                }
            }
            catch (Exception ex)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, ex.Message);
                errorMessage = String.Concat(errorMessage, " Inner: ");
                errorMessage = String.Concat(errorMessage, ex.InnerException.Message);

                MessageBox.Show(errorMessage, "Error");
            }
            return (result> 0? true: false);
        }
        
        private bool InsertTest(OracleConnection conn)
        {
            int result = 0;
            string insertQuery = "INSERT INTO LiegenschaftType (AUTO_ID, Liegenschaftsnummer, Liegenschaftsbezeichnung) VALUES ('1','1234567890','MUSTER_KAS')";
            try
            {
                OracleCommand cmd = new OracleCommand(insertQuery, conn);
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string message = ex.Message;
                MessageBox.Show(message);
            }
            return (result == 1? true: false);
        }

        private int GetLastAutoID(OracleConnection conn, string table)
        {
            int result = -1;
            string query = string.Format("SELECT count(*) AS LAST_AUTO_ID FROM {0}", table);
            OracleCommand cmd = new OracleCommand(query, conn);
            OracleDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                result = reader.GetInt32(0);
            }
            return result;
        }
        
        private Identifikation GetIsybauObject(string isybauFilePath)
        {
            XmlSerializer xs = new XmlSerializer(typeof(Identifikation));
            TextReader reader = new StreamReader(isybauFilePath);
            Identifikation result = xs.Deserialize(reader) as Identifikation;
            reader.Close();
            return result;
        }

        private OracleConnection InitialOracleConnection(string host, string service, string user, string password)
        {
            try
            {
                //string connString = string.Format("Direct=true;Host={0};Service Name={1};User Id={2};Password={3};", host, service, user, password);
					string connString = string.Format("Service Name={0};User Id={1};Password={2};", service, user, password);
                OracleConnection conn = new OracleConnection(connString);
                if (conn.State != System.Data.ConnectionState.Open)
                {
                    conn.Open();
                }
                return conn;
            }
            catch (Exception ex)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, ex.Message);
                errorMessage = String.Concat(errorMessage, " Inner: ");
                errorMessage = String.Concat(errorMessage, ex.InnerException.Message);

                MessageBox.Show(errorMessage, "Error");
            }
            return null;
        }
    }
}